package com.order.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.order.dto.User;

public class UserDAO {

	public User vaidateUser(String userName, String password)
			throws SQLException, ClassNotFoundException {

		User user = null;
		String selectUser = "select * from user_training where password=? and user_name = ? ";

		PreparedStatement preparedStatement = getMySqlConnection()
				.prepareStatement(selectUser);

		preparedStatement.setString(1, password);

		preparedStatement.setString(2, userName);

		if (preparedStatement.execute()) {

			ResultSet resultSet = preparedStatement.getResultSet();

			while (resultSet.next()) {

				user = new User();

				user.setId(resultSet.getInt("id"));
				user.setFirstName(resultSet.getString("first_name"));

				user.setLastName(resultSet.getString("last_name"));

				user.setPassword(resultSet.getString("password"));
				user.setUserName(resultSet.getString("user_name"));
				
				return user;

			}

		}
		return null;

	}

	public void testPrepareStatment() throws SQLException,
			ClassNotFoundException {

		String updateStmt = "update  user_training set password=? where password = ? ";

		PreparedStatement preparedStatement = getMySqlConnection()
				.prepareStatement(updateStmt);

		preparedStatement.setString(1, "test1_pwd");
		preparedStatement.setString(2, "password1");

		System.out.println("Executing first time ----- "
				+ preparedStatement.execute());

		preparedStatement.setString(1, "test2_pwd");
		preparedStatement.setString(2, "password2");

		System.out.println("Executing Second time ----- "
				+ preparedStatement.execute());

	}

	public void testPrepareStatmentSelect() throws SQLException,
			ClassNotFoundException {

		String updateStmt = "select * from user_training  where password like ? ";

		PreparedStatement preparedStatement = getMySqlConnection()
				.prepareStatement(updateStmt);

		preparedStatement.setString(1, "%test%");

		if (preparedStatement.execute()) {

			ResultSet resultSet = preparedStatement.getResultSet();

			while (resultSet.next()) {
				System.out.println("Id of the Users is  -- "
						+ resultSet.getInt(1));
			}

		}
	}

	public void testCallbleStatment() throws SQLException,
			ClassNotFoundException {

		String callbleStmt = "call updateUserById(1)";

		CallableStatement callableStatement = getMySqlConnection().prepareCall(
				callbleStmt);
		System.out.println(callableStatement.execute());

		// 1 st kind getMySqlConnection().createStatement()

		// 2 nd Kind getMySqlConnection().prepareStatement("Pre compiles sql");

		// 3 rd kind getMySqlConnection().prepareCall("Stored procedure");

	}

	public void updateStatment() throws SQLException, ClassNotFoundException {

		Statement statement = getMySqlConnection().createStatement();

		int noOfUpdated = statement
				.executeUpdate("update  user_training set password= 'password' where password = 'test2_pwd'");

		System.out.println("No Of DB records Updated .... " + noOfUpdated);

	}

	public void deleteStatment() throws SQLException, ClassNotFoundException {

		Statement statement = getMySqlConnection().createStatement();

		int noOfUpdated = statement
				.executeUpdate("delete from  user_training where  user_name like '%test%';");

		System.out.println("No Of DB records Updated .... " + noOfUpdated);

	}

	public Connection getMySqlConnection() throws ClassNotFoundException,
			SQLException

	{
		// Load the driver Jar in to classpath - Step 1

		// Load the Driver Class using Class.forName - Step 2 (Register the
		// Driver)

		Class.forName("com.mysql.jdbc.Driver");

		String DB_URL = "jdbc:mysql://localhost:3306/training";

		// Create Connection based on Driver loaded Above by passing DB URL
		// and user name/Password --- Step 3
		Connection connection = DriverManager.getConnection(DB_URL, "root",
				"root");

		return connection;

	}

}
